﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace SWEN_HMS.Classes
{
    public class RoomDBManager
    {
        public static int createBooking(Booking b)
        {
            int rowadded = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Booking(firstName,lastName,contactNo,address,email,noOfAdult,noOfChild,roomNo,roomType,paymentType,cardholderName,cardType,cardNo,expDate,cardCVC,checkIn,checkOut,noOfDay,remarks,staffID,checkInStatus) VALUES" +
                    "(@firstName,@lastName,@contactNo,@address,@email,@noOfAdult,@noOfChild,@roomNo,@roomType,@paymentType,@cardholderName,@cardType,@cardNo,@expDate,@cardCVC,@checkIn,@checkOut,@noOfDay,@remarks,@staffID,@checkInStatus)";

                comm.Parameters.AddWithValue("@firstName", b.firstName);
                comm.Parameters.AddWithValue("@lastName", b.lastName);
                comm.Parameters.AddWithValue("@contactNo", b.contactNo);
                comm.Parameters.AddWithValue("@address", b.address);
                comm.Parameters.AddWithValue("@email", b.email);
                comm.Parameters.AddWithValue("@noOfAdult", b.noOfAdult);
                comm.Parameters.AddWithValue("@noOfChild", b.noOfChild);
                comm.Parameters.AddWithValue("@roomNo", b.roomNo);
                comm.Parameters.AddWithValue("@roomType", b.roomType);
                comm.Parameters.AddWithValue("@paymentType", b.paymentType);
                comm.Parameters.AddWithValue("@cardholderName", b.cardholderName);
                comm.Parameters.AddWithValue("@cardType", b.cardType);
                comm.Parameters.AddWithValue("@cardNo", b.cardNo);
                comm.Parameters.AddWithValue("@expDate", b.expDate);
                comm.Parameters.AddWithValue("@cardCVC", b.cardCVC);
                comm.Parameters.AddWithValue("@checkIn", DateTime.Parse(b.checkIn));
                comm.Parameters.AddWithValue("@checkOut", DateTime.Parse(b.checkOut));
                comm.Parameters.AddWithValue("@noOfDay", b.noOfDay);
                comm.Parameters.AddWithValue("@remarks", b.remarks);
                comm.Parameters.AddWithValue("@staffID", b.staffID);
                comm.Parameters.AddWithValue("@checkInStatus", b.checkInStatus);
                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static ArrayList getDetailByRoomType(string roomType)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomNo from Room WHERE roomType=@roomType AND roomStatus='Available'";
                comm.Parameters.AddWithValue("@roomType", roomType);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int roomNo = Convert.ToInt32(dr["roomNo"]);
                    result.Add(roomNo);

                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Booking getDetailByBookingID(int bookingID)
        {
            Booking b = new Booking();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from Booking WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", bookingID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    b = new Booking();
                    b.bookingID = Convert.ToInt32(dr["bookingID"]);
                    b.firstName = (string)dr["firstName"];
                    b.lastName = (string)dr["lastName"];
                    b.contactNo = (string)dr["contactNo"];
                    b.address = (string)dr["address"];
                    b.email = (string)dr["email"];
                    b.noOfChild = Convert.ToInt32(dr["noOfChild"]);
                    b.noOfAdult = Convert.ToInt32(dr["noOfAdult"]);
                    b.roomNo = Convert.ToInt32(dr["roomNo"]);
                    b.roomType = (string)dr["roomType"];
                    b.paymentType = (string)dr["paymentType"];
                    b.cardholderName = (string)dr["cardholderName"];
                    b.cardType = (string)dr["cardType"];
                    b.cardNo = (string)dr["cardNo"];
                    b.expDate = (string)dr["expDate"];
                    b.cardCVC = (string)dr["cardCVC"];
                    b.checkIn = Convert.ToString((DateTime)dr["checkIn"]);
                    b.checkOut = Convert.ToString((DateTime)dr["checkOut"]);
                    b.remarks = (string)dr["remarks"];
                    b.staffID = Convert.ToInt32(dr["staffID"]);
                    b.checkInStatus = (string)dr["checkInStatus"];
                    b.noOfDay = Convert.ToInt32(dr["noOfDay"]);
                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return b;
        }

        public static int updateRoomStatus(Room r)
        {
            int rowupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET roomStatus=@roomStatus,noOfAdult=@noOfAdult,noOfChild=@noOfChild WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomStatus", r.roomStatus);
                comm.Parameters.AddWithValue("@roomNo", r.roomNo);
                comm.Parameters.AddWithValue("@noOfAdult", r.noOfAdult);
                comm.Parameters.AddWithValue("@noOfChild", r.noOfChild);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int addSubscription(Subscription s)
        {
            int rowadded = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Subscription(firstName,lastName,email) VALUES" +
                    "(@firstName,@lastName,@email)";

                comm.Parameters.AddWithValue("@firstName", s.firstName);
                comm.Parameters.AddWithValue("@lastName", s.lastName);
                comm.Parameters.AddWithValue("@email", s.email);

                rowadded = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;

        }

        public static Subscription searchForSubscription(string email)
        {
            Subscription sn = new Subscription();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * FROM Subscription WHERE email=@email";
                comm.Parameters.AddWithValue("@email", email);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    sn = new Subscription();
                    sn.email = (string)dr["email"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return sn;
        }

        public static Booking checkForBooking(string firstname, string lastname)
        {
            Booking bk = new Booking();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * FROM Booking WHERE firstname=@firstname AND lastname=@lastname";
                comm.Parameters.AddWithValue("@firstname", firstname);
                comm.Parameters.AddWithValue("@lastname", lastname);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    bk = new Booking();
                    bk.firstName = (string)dr["firstName"];
                    bk.lastName = (string)dr["lastName"];
                    bk.contactNo = (string)dr["contactNo"];
                    bk.address = (string)dr["address"];
                    bk.email = (string)dr["email"];
                    bk.noOfAdult = (int)dr["noOfAdult"];
                    bk.noOfChild = (int)dr["noOfChild"];
                    bk.roomNo = (int)dr["roomNo"];
                    bk.roomType = (string)dr["roomType"];
                    bk.paymentType = (string)dr["paymentType"];
                    bk.cardholderName = (string)dr["cardholderName"];
                    bk.cardType = (string)dr["cardType"];
                    bk.cardNo = (string)dr["cardNo"];
                    bk.expDate = (string)dr["expDate"];
                    bk.cardCVC = (string)dr["cardCVC"];
                    bk.checkIn = Convert.ToString((DateTime)dr["checkIn"]);
                    bk.checkOut = Convert.ToString((DateTime)dr["checkOut"]);
                    bk.remarks = (string)dr["remarks"];
                    bk.checkInStatus = (string)dr["checkInStatus"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return bk;
        }

        public static int createInvoiceID(int bookingID)
        {
            int rowadded = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(bookingID) VALUES" + "(@bookingID)";
                comm.Parameters.AddWithValue("@bookingID", bookingID);
                rowadded = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static int updateCheckInStatus(int b)
        {
            int rowupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Booking SET checkInStatus=@checkInStatus WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", b);
                comm.Parameters.AddWithValue("@checkInStatus", "True");

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int updateCheckInTime(int r)
        {
            int rowupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET checkInTime=@checkInTime WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", r);
                comm.Parameters.AddWithValue("@checkInTime", System.DateTime.Now.ToShortTimeString());

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int deleteBooking(int d)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE From Booking WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", d);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static string checkForCheckInStatus(int roomNo)
        {
            string status = "";

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select bookingID = (Select max(bookingID) From Booking),checkInStatus From Booking Where roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    status = (string)dr["checkInStatus"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return status;
        }

        public static int getBookingID(int roomID)
        {
            int room = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select bookingID = (Select max(bookingId) From Booking) From Booking Where roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomID);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    room = (int)dr["bookingID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return room;
        }

        public static int getInvoiceID(int bookingID)
        {
            int id = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select invoiceID FROM Invoice WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", bookingID);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    id = (int)dr["invoiceID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return id;
        }

        public static int addInvoiceItem(InvoiceItem i)
        {
            int rowadded = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO InvoiceItem(invoiceID,itemID,description,quantity,unitPrice) VALUES" +
                    "(@invoiceID,@itemID,@description,@quantity,@unitPrice)";

                comm.Parameters.AddWithValue("@invoiceID", i.invoiceID);
                comm.Parameters.AddWithValue("@itemID", i.itemID);
                comm.Parameters.AddWithValue("@description", i.description);
                comm.Parameters.AddWithValue("@quantity", i.quantity);
                comm.Parameters.AddWithValue("@unitPrice", i.unitPrice);

                rowadded = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;

        }

        public static int changeRoomStatus(int roomNo, string roomStatus)
        {
            int rowupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET roomStatus=@roomStatus WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                comm.Parameters.AddWithValue("@roomStatus", roomStatus);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static string checkRoomStatus(int roomNo)
        {
            string rm = "";

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomStatus From Room Where roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    rm = (string)dr["roomStatus"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rm;


        }

        public static string checkInStatus(int roomNo)
        {
            string CIStatus = "";

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select checkInStatus From Booking Where roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    CIStatus = (string)dr["checkInStatus"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return CIStatus;
        }

        public static int updateRoomAvailability(int d)
        {
            int rowupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET noOfAdult=@noOfAdult,noOfChild=@noOfChild,roomStatus=@roomStatus WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@noOfAdult", 0);
                comm.Parameters.AddWithValue("@noOfChild", 0);
                comm.Parameters.AddWithValue("@roomStatus", "Available");
                comm.Parameters.AddWithValue("@roomNo", d);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }
       
        public static int updateRoomBookingInformation(Booking b)
        {
            int rowupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Booking SET roomType=@roomType,roomNo=@roomNo, noOfAdult=@noOfAdult,noOfChild=@noOfChild,checkIn=@checkIn, checkOut=@checkOut, noOfDay=@noOfDay WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@roomType", b.roomType);
                comm.Parameters.AddWithValue("@roomNo", b.roomNo);
                comm.Parameters.AddWithValue("@noOfAdult", b.noOfAdult);
                comm.Parameters.AddWithValue("@noOfChild", b.noOfChild);
                comm.Parameters.AddWithValue("@checkIn", DateTime.Parse(b.checkIn));
                comm.Parameters.AddWithValue("@checkOut", DateTime.Parse(b.checkOut));
                comm.Parameters.AddWithValue("@noOfDay", b.noOfDay);
                comm.Parameters.AddWithValue("@bookingID", b.bookingID);
                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }
    }
}